package DAO.MysqlDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import DAO.InterfaceDAO.ExerciceDAO;
import Factories.FactoryMySqlDAO;
import Model.Correction;
import Model.Enonce;
import Model.Exercice;

public class ExerciceDAO_Mysql implements ExerciceDAO{

	private PreparedStatement psCreate, psRead, psUpdate, psDelete, psFind, psGetEnonce, psGetCorrection, psFileName, psListType,
	psListTheme, psCreateEnonce, psCreateCorrection;

	public ExerciceDAO_Mysql(Connection cx) {

		try {
			psCreateEnonce= cx.prepareStatement("INSERT INTO enonce SET contenu = ? ",PreparedStatement.RETURN_GENERATED_KEYS);
			psCreateCorrection = cx.prepareStatement("INSERT INTO correction SET contenu =?",PreparedStatement.RETURN_GENERATED_KEYS);
			psCreate = cx.prepareStatement("INSERT INTO exercice SET " +
					"titre= ? ," +
					"theme= ? ," +
					"type= ? ," +
					"difficulte= ? ," +
					"enonce= ? ," +
					"correction= ? ," +
					"library= ? ," +
					"auteur= ? ," +
			"nom_plugin= ? ");
			psRead   = cx.prepareStatement("SELECT * FROM exercice WHERE id = ?");
			//psUpdate = cx.prepareStatement("UPDATE  SET  WHERE ");
			psDelete = cx.prepareStatement("DELETE FROM exercice WHERE nom = ?");
			psFind   = cx.prepareStatement("SELECT * FROM exercice");
			psGetEnonce = cx.prepareStatement("SELECT contenu FROM enonce WHERE id=?");
			psGetCorrection = cx.prepareStatement("SELECT contenu FROM correction WHERE id=?");
			psFileName = cx.prepareStatement("SELECT nom_plugin FROM type WHERE type=?");
			psListType = cx.prepareStatement("SELECT * FROM type");
			psListTheme = cx.prepareStatement("SELECT * FROM theme");

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public boolean create(Exercice e) {
		try {
			int idE = 0 ;
			psCreateEnonce.setString(1, e.getEnonce().getContenu());
			psCreateEnonce.executeUpdate();
			ResultSet rs = psCreateEnonce.getGeneratedKeys();
			if(rs.next()) idE = rs.getInt(1); 

			int idC = 0; 
			psCreateCorrection.setString(1, e.getCorrection().getContenu());
			psCreateCorrection.executeUpdate();
			rs = psCreateCorrection.getGeneratedKeys();
			if(rs.next()) idC = rs.getInt(1); 


			psCreate.setString(1, e.getTitre());
			psCreate.setString(2, e.getTheme());
			psCreate.setString(3, e.getType());
			psCreate.setInt(4, e.getDifficulte());
			psCreate.setInt(5, idE);
			psCreate.setInt(6, idC);
			psCreate.setString(7, "1");
			psCreate.setInt(8, e.getAuteur());
			psCreate.setString(9, e.getNomPlugIn());
			psCreate.executeUpdate();
			return true;
		} catch (SQLException e1) {
			e1.printStackTrace();
		}		
		return false;
	}

	@Override
	public Exercice read(int id) {
		Exercice exo = null;
		try {
			psRead.setInt(1, id);
			ResultSet rs = psRead.executeQuery();
			if(rs.next())
			{
				String fileName = rs.getString("nom_plugin");
				Class<?> c = Class.forName(fileName);	
				exo = (Exercice) c.newInstance();
				exo.setTitre(rs.getString("titre"));
				exo.setTheme(rs.getString("theme"));
				exo.setType(rs.getString("type"));
				exo.setDifficulte(rs.getInt("difficulte"));
				exo.setEnonce(getEnonce(rs.getInt("enonce")));
				exo.setCorrection(getCorrection(rs.getInt("correction")));
				exo.setId(id);
				exo.setLibrary(FactoryMySqlDAO.getInstance().createLibDAO().read(rs.getString("library")));
				exo.setAuteur(rs.getInt("auteur"));
				exo.setNomPlugIn(rs.getString("nom_plugin"));	
			}		
		} catch (SQLException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}		
		return exo;
	}

	@Override
	public boolean update(Exercice e) {
		return false;
	}

	@Override
	public boolean delete(Exercice e) {
		try {
			psDelete.setInt(1, e.getId());
			psDelete.executeUpdate();
			return true;
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		return false;
	}

	@Override
	public String nomPlugIn(String titre) {
		try {
			psFileName.setString(1, titre);
			ResultSet rs = psFileName.executeQuery();
			rs.next();
			return rs.getString("nom_plugin");
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	@Override
	public Enonce getEnonce(int id) {
		try {
			psGetEnonce.setInt(1, id);
			ResultSet rs = psGetEnonce.executeQuery();
			rs.next();
			return new Enonce(rs.getString("contenu"));
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return null;
	}

	@Override
	public Correction getCorrection(int id) {
		try {
			psGetCorrection.setInt(1, id);
			ResultSet rs = psGetCorrection.executeQuery();
			rs.next();
			return new Correction(rs.getString("contenu"));
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return null;
	}

	@Override
	public ArrayList<String> listeTypeExercice() {
		try {
			ResultSet r = psListType.executeQuery();
			ArrayList<String> result = new ArrayList<String>();
			while(r.next()) {
				result.add(r.getString("type"));
			}
			return result;

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public ArrayList<String> listeTheme() {
		try {
			ResultSet r = psListTheme.executeQuery();
			ArrayList<String> result = new ArrayList<String>();
			while(r.next()) {
				result.add(r.getString("theme"));
			}
			return result;

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

}
